use std::env;

use dotenv::dotenv;
use futures::{StreamExt, TryFutureExt};
use sqlx::{Error, FromRow, Pool};
use sqlx_mysql::{MySql, MySqlPool};
use structopt::StructOpt;

#[derive(StructOpt)]
struct Args {
    #[structopt(subcommand)]
    cmd: Option<Command>,
}

#[derive(StructOpt)]
enum Command {
    Add { description: String },
    Search { id: Option<u64>, done: Option<i8> },
    Done { id: u64 },
    Desc { level: String },
}

#[tokio::main(flavor = "current_thread")]
async fn main() -> anyhow::Result<()> {
    dotenv().ok();
    let args = Args::from_args_safe()?;
    // let pool = MySqlPool::connect("mysql://root:root@localhost/sqlx_db").await?;
    let pool = MySqlPool::connect(&env::var("DATABASE_URL")?).await?;

    match args.cmd {
        Some(Command::Add { description }) => {
            println!("Adding new todo with description '{description}'");
            let todo_id = add_todo(&pool, description).await?;
            println!("Added new todo with id {todo_id}");
        }
        Some(Command::Done { id }) => {
            println!("Marking todo {id} as done");
            if complete_todo(&pool, id).await? {
                println!("Tod {id} is marked as done");
            } else {
                println!("Invalid id {id}");
            }
        }
        Some(Command::Search { id, done }) => {
            println!("Search todo .condition id {:?} , done {:?}", id, done);
            let res = search(id, done, &pool).await?;
            println!("Search result: {:?}", res);
        }
        Some(Command::Desc {level})=>{
            query_by_desc(level,&pool).await?;
        }
        None => {
            println!("Printing list of all todos");
            list_todos(&pool).await?;
        }
    }

    Ok(())
}

#[derive(Debug,FromRow)]
struct Todos {
    id: u64,
    description: String,
    done: i8,
    level: String,
}

async fn query_by_desc(level: String,pool: &MySqlPool)->anyhow::Result<()>{
    println!("desc: {level}");

    // r#防注入
    let r:Result<Todos,Error> = sqlx::query_as("SELECT id,description,done,level FROM todos where level = ? and description like concat('%',?,'%')")
        .bind(level)
        .bind("ell ,'%') and id like concat('%',2")
        .fetch_one(pool)
        .await;

    match r {
        Ok(t) => {
            println!("r: {:?}", t);
        }
        Err(e) => {
            print!("not found.{}",e);
        }
    }

    Ok(())
}

async fn search(
    id: Option<u64>,
    done: Option<i8>,
    pool: &Pool<MySql>,
) -> anyhow::Result<Vec<Todos>> {

    let r: Todos = sqlx::query_as("SELECT id,description,done FROM todos where id = ? AND done = ?")
        .bind(id.unwrap())
        .bind(done.unwrap())
        .fetch_one(pool)
        .await?;

    // let mut query = sqlx::query("SELECT id,description,done FROM todos");

    // if let Some(id) = id{
    //     query = query.and_then("id = ?", id);
    // }

    println!("r: {:?}", r);

    Ok(vec![])
}

async fn add_todo(pool: &MySqlPool, description: String) -> anyhow::Result<u64> {
    let todo_id = sqlx::query!(r#"INSERT INTO todos (description) VALUES (?)"#, description)
        .execute(pool)
        .await?
        .last_insert_id();

    Ok(todo_id)
}

async fn complete_todo(pool: &MySqlPool, id: u64) -> anyhow::Result<bool> {
    let rows_affected = sqlx::query!(r#"UPDATE todos SET done = TRUE WHERE id = ? "#, id)
        .execute(pool)
        .await?
        .rows_affected();

    Ok(rows_affected > 0)
}

async fn list_todos(pool: &MySqlPool) -> anyhow::Result<()> {
    let recs = sqlx::query!(r#"SELECT id,description,done FROM todos ORDER BY id"#)
        .fetch_all(pool)
        .await?;

    for rec in recs {
        println!(
            "-[{}] {}: {}",
            if rec.done != 0 { "x" } else { " " },
            rec.id,
            &rec.description
        );
    }

    Ok(())
}
